package com.hifar.test;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class TempUpuseridcard {

    public void updateUserIdCard() {
        String excelPath = "D:\\ideaProjects\\tdm8357-server\\src\\test\\resources\\userInfo.xls";
        String url = "jdbc:mysql://localhost:3306/tdm8357";
        String username = "root";
        String password = "123456";

        try (FileInputStream fis = new FileInputStream(excelPath);
             Workbook workbook = new HSSFWorkbook(fis);
             Connection conn = DriverManager.getConnection(url, username, password)) {

            Sheet sheet = workbook.getSheetAt(0);
            List<String[]> dataList = new ArrayList<>();

            // 读取Excel数据
            for (Row row : sheet) {
                if (row.getRowNum() == 0) continue; // 跳过表头

                Cell userCodeCell = row.getCell(0); // A列 - 用户账号
                Cell idCardCell = row.getCell(9);   // J列 - 身份证号

                if (userCodeCell != null && idCardCell != null) {
                    String userCode = userCodeCell.toString().trim();
                    String idCard = idCardCell.toString().trim();
                    if (!userCode.isEmpty() && !idCard.isEmpty()) {
                        dataList.add(new String[]{userCode, idCard});
                    }
                }
            }

            // 更新数据库
            String updateSql = " UPDATE tdm8357.base_org_user SET id_card = ? " +
                    " WHERE user_code = ? AND del_flag = 0 AND status!=99 ";

            try (PreparedStatement pstmt = conn.prepareStatement(updateSql)) {
                for (String[] data : dataList) {
                    pstmt.setString(1, data[1]); // id_card
                    pstmt.setString(2, data[0]); // user_code
                    pstmt.executeUpdate();
                }
            }

            System.out.println("数据更新完成，共处理 " + dataList.size() + " 条记录");

        } catch (IOException | SQLException e) {
            e.printStackTrace();
            System.out.println("更新过程中发生错误: " + e.getMessage());
        }
    }

    public static void main(String[] args) {
        System.out.println("开始执行身份证号更新程序...");
        TempUpuseridcard updater = new TempUpuseridcard();
        updater.updateUserIdCard();
        System.out.println("程序执行完成。");
    }
}
